package exceltocard;

import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CsvToExcel {

    public static File ConvertorToExcel(File csvfile, File excelFile)
            throws FileNotFoundException, IOException {
        DataInputStream in = new DataInputStream(new FileInputStream(csvfile));
        //确定文件类型
        String filename = csvfile.getName();
        String type = filename.substring(filename.lastIndexOf(".") + 1);
        String excelname = excelFile.getName();
        String exceltype = excelname.substring(excelname.lastIndexOf(".") + 1);
        // 编码
        byte[] b = new byte[3];
        in.read(b);
        in.close();
        String code;
        if (b[0] == -17 && b[1] == -69 && b[2] == -65) {
            code = "UTF-8";
        } else {
            code = "GBK";
        }
        in.close();
        in = new DataInputStream(new FileInputStream(csvfile));
        BufferedReader br = new BufferedReader(new InputStreamReader(in, code));
        int titleCount = 0;
        List<Vcard> dataEntities = new ArrayList<Vcard>(titleCount);
        if(type.equals("csv")) {
        //CSV读值
        CSVParser records = CSVFormat.EXCEL.parse(br);
        List<CSVRecord> iterator = records.getRecords();
        // 先处理表头
        CSVRecord titleRecord = iterator.get(0);
        titleCount = titleRecord.size();
        // 先处理表头
        List<String> list = new ArrayList<String>(titleCount);
        for (int j = 0; j < titleCount; j++) {
            list.add(titleRecord.get(j));
        }
        br.close();
        // 开始处理value
        List<String>[] values = new ArrayList[titleCount];
        // 初始化数组
        for (int i1 = 0; i1 < values.length; i1++) {
            List<String> value = new ArrayList<String>();
            values[i1] = value;
        }
        // 循环读取value
        for (int i1 = 1; i1 < iterator.size(); i1++) {
            CSVRecord csvRecord = iterator.get(i1);
            int count = csvRecord.size();
            for (int j = 0; j < count; j++) {
                List<String> value = values[j];
                value.add(csvRecord.get(j));
                values[j] = value;
            }
        }
        //下面为csv转excel,并创建。
        for (int i1 = 0; i1 < titleCount; i1++) {
            Vcard entity = new Vcard();
            entity.setTitle(list.get(i1));
            entity.setValue(values[i1]);
            dataEntities.add(entity);
        }
        }
        //vcf转excel
        if (type.equals("vcf")) {//vcf转excel
            String scard = "";
            StringBuffer scards = new StringBuffer();
            BufferedReader in1 = new BufferedReader(new FileReader(csvfile));
            while ((scard = in1.readLine()) != null) {
                scards.append(scard + ",");
            }

            Pattern pattern = Pattern.compile("(,N:|CELL:)([\u4e00-\u9fa5]+|[\\w]+)(;|,END)");
//            Pattern pattern2 = Pattern.compile("(CELL:)([\u0391-\uffe5]*|[\\w-]*|[\\s]+)(,END)");
            Matcher matcher = pattern.matcher(scards);
            //正则分组
            List<String> titlen = new ArrayList<String>();
            List<String> valuel = new ArrayList<String>();
            int i = 0;
            while (matcher.find()) {
                //匹配表头
                if (i == 0) {
                    titlen.add(matcher.group(2));
                }
                if (i == 1) {
                    titlen.add(matcher.group(2));
                }
                if(i>=2) {//匹配表值list
                    valuel.add(matcher.group(2));
                }
                i++;
            }
            titleCount = titlen.size();
            // 开始处理value
            List<String>[] valuels = new ArrayList[titleCount];
            // 循环读取value
            for (int i1 = 0; i1 < titleCount; i1++) {
                List<String> value = new ArrayList<String>();
                for (int j = 0; j < (valuel.size()/2); j++) {
                    int k=j;
                    if(i1>0){
                        k= j+1;
                    }
                    value.add(valuel.get(k*2-i1));
                }
                valuels[i1] = value;
            }
            for (int i1 = 0; i1 < titleCount; i1++) {
                Vcard entity = new Vcard();
                entity.setTitle(titlen.get(i1));
                entity.setValue(valuels[i1]);
                dataEntities.add(entity);
            }
            in1.close();
        }
//下面为csv转excel,并创建。

        if (!excelFile.exists()) {
            excelFile.createNewFile();
        }
        createExcel(excelFile,dataEntities,exceltype);
        return excelFile;
    }
    public static void createExcel(File file, List<Vcard> entities,String exceltype) {
        Workbook xwb=null;
        if("xlsx".equals(exceltype)) {
            xwb = new XSSFWorkbook();
        }
        else {
            xwb = new HSSFWorkbook();
        }
        Sheet xsheet = xwb.createShee();
        Row titleRow = xsheet.createRow(0);
        for (int i = 0; i < entities.size(); i++) {
            Vcard entity = entities.get(i);
            Cell titCell = titleRow.createCell(i);
            titCell.setCellValue(entity.getTitle());
            List<String> values = entity.getValue();
            for (int j = 0; j < values.size(); j++) {
                Row valueRow = null;
                if (xsheet.getLastRowNum() > j) {
                    valueRow = xsheet.getRow(j + 1);
                } else {
                    valueRow = xsheet.createRow(j + 1);
                }
                Cell valueCell = valueRow.createCell(i);
                valueCell.setCellValue(values.get(j));
            }
        }
        try {
            if (file.exists()) {
                file.createNewFile();
            }
            FileOutputStream fout = new FileOutputStream(file);
            xwb.write(fout);
            fout.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
